﻿Imports System.Data.SqlClient
Imports System.IO

Public Class frmCorporateProductionApprovalFinancial

    Dim FolderPath As String = FTPAddress + "Production Corporate"

    Sub FillPendingTransactions()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim cmd As New SqlCommand("Select SNo,ClientNo,ClientName,EndorsType,SavedUser,SavedDate,ApprovedUser,ApprovedDate " & _
                                      "From Policies Where Done=1 and DoneFin=0 and Branch=N'" & Branch & "'", cnn)
            Dim Reader As SqlDataReader
            Dim X As Integer = 1

            Clear()
            Me.ListPendingTrans.Items.Clear()

            cnn.Open()
            Reader = cmd.ExecuteReader
            While (Reader.Read)
                Dim Item As New ListViewItem
                Item.Text = Reader.Item("SNo")
                Item.SubItems.Add(X)
                Item.SubItems.Add(Reader.Item("ClientNo"))
                Item.SubItems.Add(Reader.Item("ClientName"))
                Item.SubItems.Add(Reader.Item("EndorsType"))
                Item.SubItems.Add(Reader.Item("SavedUser"))
                Item.SubItems.Add(CDate(Reader.Item("SavedDate")).ToString("dd-MMM-yyyy HH:mm"))
                Item.SubItems.Add(Reader.Item("ApprovedUser"))
                Item.SubItems.Add(CDate(Reader.Item("ApprovedDate")).ToString("dd-MMM-yyyy HH:mm"))

                Me.ListPendingTrans.Items.Add(Item)
                X += 1
            End While
            cnn.Close()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub FillProducers()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim ProducersList As ArrayList = GetProducersList()

            Me.CombProducers.Items.Clear()

            For Each Producer As String In ProducersList
                Me.CombProducers.Items.Add(Producer)
            Next

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub Clear()
        Me.ErrProv.Clear()
        Me.txtNo.Clear()
        Me.txtClientNo.Clear()
        Me.txtClientName.Clear()
        Me.RDirectBusiness.Checked = True
        Me.CombDirectBusiness.SelectedIndex = -1
        Me.txtAccNo.Clear()
        Me.txtPolicyNo.Clear()
        Me.txtType.Clear()
        Me.txtEndorsType.Clear()
        Me.txtEndorsNo.Clear()
        Me.txtInsurComp.Clear()
        Me.txtInsurClasses.Clear()
        Me.txtIssueDate.Clear()
        Me.txtPeriodFRm.Clear()
        Me.txtSumInsur.Clear()
        Me.txtClientDNCNNo.Clear()
        Me.txtTotalPremium.Clear()
        Me.txtNetPremium.Clear()
        Me.txtCompDNCNNo.Clear()
        Me.txtCompComm.Clear()
        Me.CombProducers.SelectedIndex = -1
        Me.txtProdCommPerc.Value = 0
        Me.txtProdCommAmount.Text = "0.00"
        Me.GridPaymentsSchedule.Rows.Clear()
        Me.CombProducers.SelectedIndex = -1
        Me.txtProdCommPerc.Value = 0
        Me.txtProdCommAmount.Text = "0.00"
        Me.GridProducersCommissions.Rows.Clear()
        Me.GridVoucher.Rows.Clear()
        Me.GridFiles.Rows.Clear()
    End Sub

    Function CheckPolicyNo(ByVal PolNo As String) As Boolean
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim cmd As New SqlCommand("Select IsNull(Count(*),0) From Policies Where PolicyNo=N'" & PolNo & "'", cnn)
            Dim Count As Integer

            cnn.Open()
            Count = CInt(cmd.ExecuteScalar)
            cnn.Close()

            If Count > 0 Then
                Me.Cursor = Cursors.Default
                Return True
            Else
                Me.Cursor = Cursors.Default
                Return False
            End If
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Function

#Region "Fill Policy Details"

    Sub FillPaymentSchedule()
        Try
            Me.Cursor = Cursors.WaitCursor
            Dim cmd As New SqlCommand("Select * From PaymentsSchedule Where TransNo=" & Me.ListPendingTrans.SelectedItems(0).Text, cnn)
            Dim Reader As SqlDataReader

            Me.GridPaymentsSchedule.Rows.Clear()

            cnn.Open()
            Reader = cmd.ExecuteReader
            While (Reader.Read)
                Me.GridPaymentsSchedule.Rows.Add(New String() {CDate(Reader.Item("PayDate")).ToString("dd/MM/yyyy"), _
                                                              CDbl(Reader.Item("Amount")).ToString("N2"), "Delete"})
            End While
            cnn.Close()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Public Sub FillDocuments()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim Dir As New IO.DirectoryInfo(FolderPath & "\" & Me.ListPendingTrans.SelectedItems(0).Text)
            Dim DirFiles As IO.FileInfo() = Dir.GetFiles()

            Me.GridFiles.Rows.Clear()

            ''list the names of all files in the specified directory
            For Each Fi As FileInfo In DirFiles
                Me.GridFiles.Rows.Add(New String() {Fi.FullName, Fi.Name, "Open", "Delete"})
            Next

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            MsgBox(ex.ToString)
        End Try
    End Sub

#End Region
    Sub FillBranches()
        Try
            Me.Cursor = Cursors.WaitCursor

            Me.CombDirectBusiness.Items.Clear()
            Dim Branches As ArrayList = GetBranches()

            For Each Branch As String In Branches
                Me.CombDirectBusiness.Items.Add("Direct Business - " & Branch)
            Next

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub frmApprove_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.WindowState = FormWindowState.Maximized

        FillBranches()
        FillProducers()
        FillPendingTransactions()
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
        Me.Close()
    End Sub

    Private Sub GridPaymentsSchedule_CellClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles GridPaymentsSchedule.CellClick
        If e.ColumnIndex = 2 Then
            If MsgBox("Confirm Delete?", MsgBoxStyle.YesNo, Me.Text) = MsgBoxResult.Yes Then
                Me.GridPaymentsSchedule.Rows.RemoveAt(e.RowIndex)
            End If
        End If
    End Sub

    Private Sub txtPolicyNo_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtPolicyNo.Leave
        If Me.txtPolicyNo.Text.Trim.Length > 0 Then
            If CheckPolicyNo(Me.txtPolicyNo.Text.Trim) = True Then
                Me.ErrProv.SetError(Me.txtPolicyNo, "Policy no. already exists")
            Else
                Me.ErrProv.Clear()
            End If
        End If
    End Sub

    Private Sub txtPolicyNo_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtPolicyNo.TextChanged
        Me.ErrProv.Clear()
    End Sub

    Private Sub txtEndorsNo_Leave(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtEndorsNo.Leave
        If Me.txtEndorsNo.Text.Trim.Length > 0 Then
            If CheckEndorsNo(Me.txtPolicyNo.Text.Trim, Me.txtEndorsNo.Text.Trim) = True Then
                Me.ErrProv.SetError(Me.txtEndorsNo, "Endorsement no. already exists")
            Else
                Me.ErrProv.Clear()
            End If
        End If
    End Sub

    Function CheckEndorsNo(ByVal PolicyNo As String, ByVal EndorsNo As String) As Boolean
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim cmd As New SqlCommand("Select IsNull(Count(*),0) From Policies Where PolicyNo=N'" & PolicyNo & "' and EndorsNo=N'" & EndorsNo & "'", cnn)
            Dim Count As Integer

            cnn.Open()
            Count = CInt(cmd.ExecuteScalar)
            cnn.Close()

            If Count > 0 Then
                Me.Cursor = Cursors.Default
                Return True
            Else
                Me.Cursor = Cursors.Default
                Return False
            End If
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Function

    Sub CalculateProdComm()
        Try
            For Each row As DataGridViewRow In Me.GridProducersCommissions.Rows
                row.Cells(Me.DGColProdComm.Index).Value = CDbl(CDbl(Me.txtCompComm.Text) * (row.Cells(Me.DGColProdPerc.Index).Value) / 100).ToString("N2")
            Next
        Catch ex As Exception

        End Try
    End Sub

    Private Sub RDirectBusiness_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RDirectBusiness.CheckedChanged
        CheckRadioProducer()
    End Sub

    Private Sub RProducer_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RProducer.CheckedChanged
        CheckRadioProducer()
    End Sub

    Private Sub txtProdCommPerc_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtProdCommPerc.ValueChanged
        Try
            Me.txtProdCommAmount.Text = CDbl(CDbl(Me.txtCompComm.Text) * Me.txtProdCommPerc.Value / 100).ToString("N2")
        Catch ex As Exception
            Me.txtProdCommAmount.Text = "0.00"
        End Try
    End Sub

    Private Sub btnAddProdComm_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddProdComm.Click
        Me.ErrProv.Clear()
        If Me.CombProducers.SelectedIndex = -1 Then
            Me.ErrProv.SetError(Me.CombProducers, "Please fill in all details")
            Me.CombProducers.Focus()

        ElseIf CDbl(Me.txtProdCommAmount.Text) = 0 Then
            Me.ErrProv.SetError(Me.txtProdCommPerc, "Please fill in all details")
            Me.txtProdCommPerc.Focus()

        Else
            Try
                Me.GridProducersCommissions.Rows.Add({Me.CombProducers.SelectedItem, Me.txtProdCommPerc.Value, Me.txtProdCommAmount.Text})

                BuildVoucher()

                Me.txtProdCommPerc.Value = 0
                Me.txtProdCommAmount.Text = "0.00"
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try

        End If
    End Sub

    Private Sub txtPolicyNo_MouseDoubleClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles txtPolicyNo.MouseDoubleClick
        If Me.txtPolicyNo.Text.Trim.Length = 0 Then
            Me.ErrProv.SetError(Me.txtPolicyNo, "Please fill in")
        Else
            Try
                Me.Cursor = Cursors.WaitCursor

                Dim dap As New SqlDataAdapter("Select * From Policies Where PolicyNo=N'" & Me.txtPolicyNo.Text.Trim & "'", cnn)
                Dim das As New DataSet

                cnn.Open()
                dap.Fill(das, "Policies")
                cnn.Close()

                Dim rpt As New ListOfPolicies
                rpt.SetDataSource(das)
                rptViewer.CrystalReportViewer1.ReportSource = rpt
                rptViewer.CrystalReportViewer1.RefreshReport()
                rptViewer.ShowDialog()

                Me.Cursor = Cursors.Default
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
                MsgBox(ex.ToString)
            End Try
        End If
    End Sub

    Private Sub GridProducersCommissions_CellClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles GridProducersCommissions.CellClick
        If e.ColumnIndex = Me.DGColProdDel.Index Then
            Me.GridProducersCommissions.Rows.RemoveAt(e.RowIndex)

            BuildVoucher()
        End If
    End Sub

    Sub CheckRadioProducer()
        If Me.RDirectBusiness.Checked = True Then
            Me.CombDirectBusiness.Enabled = True
            Me.CombProducers.SelectedIndex = -1
            Me.CombProducers.Enabled = False
            Me.txtProdCommPerc.Value = 0
            Me.txtProdCommPerc.Enabled = False
            Me.btnAddProdComm.Enabled = False
            Me.GridProducersCommissions.Rows.Clear()

        ElseIf Me.RProducer.Checked = True Then
            Me.CombDirectBusiness.Enabled = False
            Me.CombDirectBusiness.SelectedIndex = -1
            Me.CombProducers.Enabled = True
            Me.txtProdCommPerc.Enabled = True
            Me.btnAddProdComm.Enabled = True
        End If
    End Sub

    Private Sub ListPendingTrans_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListPendingTrans.SelectedIndexChanged
        Try
            Clear()
            If Me.ListPendingTrans.SelectedItems.Count > 0 Then
                Me.Cursor = Cursors.WaitCursor

                Dim cmd As New SqlCommand("Select * From Policies Where SNo=" & Me.ListPendingTrans.SelectedItems(0).Text, cnn)
                Dim Reader As SqlDataReader

                cnn.Open()
                Reader = cmd.ExecuteReader
                While (Reader.Read)
                    'Check Producer
                    If Me.GridProducersCommissions.Rows.Count = -1 Then
                        Me.RDirectBusiness.Checked = True
                        Me.CombProducers.Enabled = False
                    Else
                        Me.RProducer.Checked = True
                        Me.CombProducers.Enabled = True
                    End If
                    Me.txtNo.Text = Reader.Item("PolEndNo")
                    Me.txtClientNo.Text = Reader.Item("ClientNo")
                    Me.txtClientName.Text = Reader.Item("ClientName")
                    Me.txtAccNo.Text = Reader.Item("AccNo")
                    Me.txtPolicyNo.Text = Reader.Item("PolicyNo")
                    Me.txtType.Text = "Policy"
                    Me.txtEndorsType.Text = Reader.Item("EndorsType")
                    Me.txtEndorsNo.Text = Reader.Item("EndorsNo")
                    Me.txtInsurComp.Text = Reader.Item("InsurComp")
                    Me.txtInsurClasses.Text = Reader.Item("ClassName")
                    Me.txtLineofBusiness.Text = Reader.Item("LineOfBusiness")
                    Me.txtIssueDate.Text = Reader.Item("IssueDate")
                    Me.txtPeriodFRm.Text = Reader.Item("PeriodFrom")
                    Me.txtPeriodTo.Text = Reader.Item("PeriodTo")

                    Me.txtSumInsur.Text = CDbl(Reader.Item("SumInsur")).ToString("N2")
                    Me.txtClientDNCNNo.Text = Reader.Item("ClientDNCNNo")
                    Me.txtTotalPremium.Text = CDbl(Reader.Item("TotalPremium")).ToString("N2")
                    Me.txtNetPremium.Text = CDbl(Reader.Item("NetPremium")).ToString("N2")
                    Me.txtCompDNCNNo.Text = Reader.Item("CompCommDNCNNo")
                    Me.txtCompComm.Text = CDbl(Reader.Item("CompComm")).ToString("N2")
                End While
                Reader.Close()
                cnn.Close()

                FillPaymentSchedule()
                FillDocuments()
                BuildVoucher()

                Me.ListPendingTrans.Focus()

                Me.Cursor = Cursors.Default
            End If
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub BuildVoucher()
        Try
            Me.Cursor = Cursors.WaitCursor

            Me.GridVoucher.Rows.Clear()

            Dim ClientAccNo, InsurCompAccNo, CommissionAccNo, ProducerAccNo, ProducerExpensesAccNo, SamaAccNo, SamaAccNoExpenses As String
            Dim AccNames(3), DescrPrem, DescrComm As String
            Dim Premium, CompanyCommission, SAMASupervFees, ProducerComm As Double

            Premium = Math.Abs(CDbl(Me.txtTotalPremium.Text)).ToString("N2")
            CompanyCommission = Math.Abs(CDbl(Me.txtCompComm.Text)).ToString("N2")
            SAMASupervFees = CompanyCommission * 1 / 100

            ClientAccNo = GetClientAccNo(CInt(Me.txtClientNo.Text))
            InsurCompAccNo = GetInsurCompAccNo(Me.txtInsurComp.Text)
            CommissionAccNo = GetInsurClassAccNo(Me.txtInsurClasses.Text)
            SamaAccNo = GetFinancialSettingsAccNo("SAMA Supervisory Fees")
            SamaAccNoExpenses = GetFinancialSettingsAccNo("SAMA Supervisory Fees - Expenses")
            ProducerExpensesAccNo = GetFinancialSettingsAccNo("Producers Commissions - Expenses")

            If CDbl(Me.txtTotalPremium.Text) > 0 Then
                If Me.txtEndorsNo.Text.Trim.Length = 0 Then
                    DescrPrem = Me.txtInsurComp.Text.Trim & ", Policy# " & Me.txtPolicyNo.Text.Trim & ", DN# " & Me.txtClientDNCNNo.Text.Trim
                    DescrComm = Me.txtInsurComp.Text.Trim & ", Policy# " & Me.txtPolicyNo.Text.Trim & ", CN# " & Me.txtCompDNCNNo.Text.Trim
                Else
                    DescrPrem = Me.txtInsurComp.Text.Trim & ", Policy# " & Me.txtPolicyNo.Text.Trim & ", Endors# " & Me.txtEndorsNo.Text.Trim & ", DN# " & Me.txtClientDNCNNo.Text.Trim
                    DescrComm = Me.txtInsurComp.Text.Trim & ", Policy# " & Me.txtPolicyNo.Text.Trim & ", Endors# " & Me.txtEndorsNo.Text.Trim & ", CN# " & Me.txtCompDNCNNo.Text.Trim
                End If
            Else
                If Me.txtEndorsNo.Text.Trim.Length = 0 Then
                    DescrPrem = Me.txtInsurComp.Text.Trim & ", Policy# " & Me.txtPolicyNo.Text.Trim & ", CN# " & Me.txtClientDNCNNo.Text.Trim
                    DescrComm = Me.txtInsurComp.Text.Trim & ", Policy# " & Me.txtPolicyNo.Text.Trim & ", DN# " & Me.txtCompDNCNNo.Text.Trim
                Else
                    DescrPrem = Me.txtInsurComp.Text.Trim & ", Policy# " & Me.txtPolicyNo.Text.Trim & ", Endors# " & Me.txtEndorsNo.Text.Trim & ", CN# " & Me.txtClientDNCNNo.Text.Trim
                    DescrComm = Me.txtInsurComp.Text.Trim & ", Policy# " & Me.txtPolicyNo.Text.Trim & ", Endors# " & Me.txtEndorsNo.Text.Trim & ", DN# " & Me.txtCompDNCNNo.Text.Trim
                End If
            End If

            '1) Client vs Insurance Company Premium
            If CDbl(Me.txtTotalPremium.Text) > 0 Then
                AccNames = GetAccNames(ClientAccNo)
                Me.GridVoucher.Rows.Add({ClientAccNo, AccNames(0), AccNames(1), AccNames(2), AccNames(3), "Search....", DescrPrem,
                                         Premium.ToString("N2"), "0.00"})

                AccNames = GetAccNames(InsurCompAccNo)
                Me.GridVoucher.Rows.Add({InsurCompAccNo, AccNames(0), AccNames(1), AccNames(2), AccNames(3), "Search....", DescrPrem,
                                         "0.00", Premium.ToString("N2")})
            Else
                AccNames = GetAccNames(ClientAccNo)
                Me.GridVoucher.Rows.Add({ClientAccNo, AccNames(0), AccNames(1), AccNames(2), AccNames(3), "Search....", DescrPrem,
                                         "0.00", Premium.ToString("N2")})

                AccNames = GetAccNames(InsurCompAccNo)
                Me.GridVoucher.Rows.Add({InsurCompAccNo, AccNames(0), AccNames(1), AccNames(2), AccNames(3), "Search....", DescrPrem,
                                         Premium.ToString("N2"), "0.00"})
            End If

            '2) Insurance Company vs Commission
            If CDbl(Me.txtCompComm.Text) > 0 Then
                AccNames = GetAccNames(InsurCompAccNo)
                Me.GridVoucher.Rows.Add({InsurCompAccNo, AccNames(0), AccNames(1), AccNames(2), AccNames(3), "Search....", DescrComm,
                                         CompanyCommission.ToString("N2"), "0.00"})

                AccNames = GetAccNames(CommissionAccNo)
                Me.GridVoucher.Rows.Add({CommissionAccNo, AccNames(0), AccNames(1), AccNames(2), AccNames(3), "Search....", DescrComm,
                                         "0.00", CompanyCommission.ToString("N2")})
            Else
                AccNames = GetAccNames(InsurCompAccNo)
                Me.GridVoucher.Rows.Add({InsurCompAccNo, AccNames(0), AccNames(1), AccNames(2), AccNames(3), "Search....", DescrComm,
                                         "0.00", CompanyCommission.ToString("N2")})

                AccNames = GetAccNames(CommissionAccNo)
                Me.GridVoucher.Rows.Add({CommissionAccNo, AccNames(0), AccNames(1), AccNames(2), AccNames(3), "Search....", DescrComm,
                                         CompanyCommission.ToString("N2"), "0.00"})
            End If

            '3) Commission vs SAMA Supervisory Fees
            If CDbl(Me.txtCompComm.Text) > 0 Then
                AccNames = GetAccNames(SamaAccNoExpenses)
                Me.GridVoucher.Rows.Add({SamaAccNoExpenses, AccNames(0), AccNames(1), AccNames(2), AccNames(3), "Search....", DescrComm,
                                         SAMASupervFees.ToString("N2"), "0.00"})

                AccNames = GetAccNames(SamaAccNo)
                Me.GridVoucher.Rows.Add({SamaAccNo, AccNames(0), AccNames(1), AccNames(2), AccNames(3), "Search....", DescrComm,
                                         "0.00", SAMASupervFees.ToString("N2")})
            Else
                AccNames = GetAccNames(SamaAccNoExpenses)
                Me.GridVoucher.Rows.Add({SamaAccNoExpenses, AccNames(0), AccNames(1), AccNames(2), AccNames(3), "Search....", DescrComm,
                                         "0.00", SAMASupervFees.ToString("N2")})

                AccNames = GetAccNames(SamaAccNo)
                Me.GridVoucher.Rows.Add({SamaAccNo, AccNames(0), AccNames(1), AccNames(2), AccNames(3), "Search....", DescrComm,
                                         SAMASupervFees.ToString("N2"), "0.00"})
            End If

            '4) Commission vs Producers Commission
            For Each row As DataGridViewRow In Me.GridProducersCommissions.Rows
                ProducerAccNo = GetProducerAccNo(row.Cells(Me.DGColProducer.Index).Value)
                ProducerComm = Math.Abs(CDbl(row.Cells(Me.DGColProdComm.Index).Value))
                If CDbl(row.Cells(Me.DGColProdComm.Index).Value) > 0 Then
                    AccNames = GetAccNames(ProducerExpensesAccNo)
                    Me.GridVoucher.Rows.Add({ProducerExpensesAccNo, AccNames(0), AccNames(1), AccNames(2), AccNames(3), "Search....", DescrComm,
                                             ProducerComm.ToString("N2"), "0.00"})

                    AccNames = GetAccNames(ProducerAccNo)
                    Me.GridVoucher.Rows.Add({ProducerAccNo, AccNames(0), AccNames(1), AccNames(2), AccNames(3), "Search....", DescrComm,
                                             "0.00", ProducerComm.ToString("N2")})
                Else
                    AccNames = GetAccNames(ProducerExpensesAccNo)
                    Me.GridVoucher.Rows.Add({ProducerExpensesAccNo, AccNames(0), AccNames(1), AccNames(2), AccNames(3), "Search....", DescrComm,
                                             "0.00", ProducerComm.ToString("N2")})

                    AccNames = GetAccNames(ProducerAccNo)
                    Me.GridVoucher.Rows.Add({ProducerAccNo, AccNames(0), AccNames(1), AccNames(2), AccNames(3), "Search....", DescrComm,
                                             ProducerComm.ToString("N2"), "0.00"})
                End If
            Next

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub GridFiles_CellClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles GridFiles.CellClick
        If e.ColumnIndex = Me.DGFilesColDocOpen.Index Then
            Try
                Me.Cursor = Cursors.WaitCursor

                System.Diagnostics.Process.Start(Me.GridFiles.CurrentRow.Cells(Me.DGFilesPath.Index).Value)

                Me.Cursor = Cursors.Default
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                MsgBox(ex.ToString)
            End Try
        End If
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnApprove.Click

        Me.ErrProv.Clear()
        If Me.ListPendingTrans.SelectedItems.Count = 0 Then
            MsgBox("Please select a policy/endorsement from the list")
            Exit Sub

        ElseIf Me.RDirectBusiness.Checked = True And Me.CombDirectBusiness.SelectedIndex = -1 Then
            Me.ErrProv.SetError(Me.CombDirectBusiness, "Please select a valid producer from the list")

        ElseIf Me.RProducer.Checked = True And Me.GridProducersCommissions.Rows.Count = -1 Then
            Me.ErrProv.SetError(Me.CombProducers, "Please select a valid producer from the list")

        Else
            Try
                Me.Cursor = Cursors.WaitCursor

                Dim cmd As New SqlCommand
                Dim Trans As SqlTransaction
                Dim MoveNo, DocSNo, Year As Integer
                Dim Descr, AmountColumn, IssueType, Producer, Abbreviation, Pattern, PatternType As String
                Dim Renewal As String = ""
                Dim TotPrem, NetPrem, ProducersComm As Double


                'Get Abbreviation
                Abbreviation = GetAbbreviation(Me.txtInsurClasses.Text)

                If Me.txtEndorsType.Text = "Refund" Then
                    TotPrem = (-1) * CDbl(Me.txtTotalPremium.Text)
                    NetPrem = (-1) * CDbl(Me.txtNetPremium.Text)
                Else
                    TotPrem = CDbl(Me.txtTotalPremium.Text)
                    NetPrem = CDbl(Me.txtNetPremium.Text)
                End If

                'Check producer
                If Me.RDirectBusiness.Checked = True Then
                    Producer = Me.CombDirectBusiness.SelectedItem

                ElseIf Me.RProducer.Checked = True Then
                    Producer = Me.CombProducers.SelectedItem
                End If
                'Calculate Producers total comm
                For Each rowProd As DataGridViewRow In Me.GridProducersCommissions.Rows
                    ProducersComm += CDbl(rowProd.Cells(Me.DGColProdComm.Index).Value)
                Next

                cnn.Open()
                Trans = cnn.BeginTransaction
                cmd.Connection = cnn
                cmd.Transaction = Trans

                'Get Year
                cmd.CommandText = "Select Year(GetDate())"
                Year = CInt(cmd.ExecuteScalar)

                cmd.CommandText = "Update Policies Set ProducerComm=@ProducerComm,FinApprovedDate=GetDate(),FinApprovedUser=@FinApprovedUser,DoneFin=1 " & _
                                  "Where SNo=" & Me.ListPendingTrans.SelectedItems(0).Text

                cmd.Parameters.Clear()
                cmd.Parameters.AddWithValue("@ProducerComm", ProducersComm)
                cmd.Parameters.AddWithValue("@FinApprovedUser", CurrentUser)
                cmd.ExecuteNonQuery()

                'Save producers commissions
                For Each row As DataGridViewRow In Me.GridProducersCommissions.Rows
                    cmd.CommandText = "Insert Into PoliciesProducers (PolNo,Producer,CommPerc,Comm) Values " & _
                                      "(@PolNo,@Producer,@CommPerc,@Comm)"

                    cmd.Parameters.Clear()
                    cmd.Parameters.AddWithValue("@PolNo", Me.ListPendingTrans.SelectedItems(0).Text)
                    cmd.Parameters.AddWithValue("@Producer", row.Cells(Me.DGColProducer.Index).Value)
                    cmd.Parameters.AddWithValue("@CommPerc", CInt(row.Cells(Me.DGColProdPerc.Index).Value))
                    cmd.Parameters.AddWithValue("@Comm", CDbl(row.Cells(Me.DGColProdComm.Index).Value))
                    cmd.ExecuteNonQuery()
                Next


                'Make Financial Voucher
                'Get account numbers
              


                'Get Move No.
                cmd.CommandText = "Select IsNull(Max(MoveNo),0) From Transactions Where Branch=N'" & Branch & "' and Month(TransDate)=Month(GetDate()) and Year(TransDate)=Year(GetDate())"
                MoveNo = CInt(cmd.ExecuteScalar) + 1

                If CDbl(Me.txtCompComm.Text) <> 0 Then
                    '1) Dr. Insurance Comp
                    cmd.CommandText = "Insert into Transactions (TransType,Source,MoveNo,AccNo,Acc1,Acc2,Acc3,Acc4,Descr,TotalOut," & _
                                      "SavedUser,TransDate,Branch,ApprovedUser,ApprovedDate) Values (@TransType,@Source,@MoveNo,@AccNo,@Acc1," & _
                                      "@Acc2,@Acc3,@Acc4,@Descr,@TotalOut,@SavedUser,GetDate(),@Branch,@ApprovedUser,GetDate())"

                    cmd.Parameters.Clear()
                    cmd.Parameters.AddWithValue("@TransType", "Journal Voucher")
                    cmd.Parameters.AddWithValue("@Source", "P")
                    cmd.Parameters.AddWithValue("@MoveNo", MoveNo)
                    'cmd.Parameters.AddWithValue("@AccNo", InsurCompAccNo)
                    cmd.Parameters.AddWithValue("@Acc1", "Assets")
                    cmd.Parameters.AddWithValue("@Acc2", "Current Assets")
                    cmd.Parameters.AddWithValue("@Acc3", "Accounts Receivable (Commission from Insurance Companies)")
                    cmd.Parameters.AddWithValue("@Acc4", Me.txtInsurComp.Text)
                    cmd.Parameters.AddWithValue("@Descr", Descr)
                    cmd.Parameters.AddWithValue("@Branch", Branch)
                    cmd.Parameters.AddWithValue("@TotalOut", CDbl(Me.txtCompComm.Text))
                    cmd.Parameters.AddWithValue("@SavedUser", CurrentUser)
                    cmd.Parameters.AddWithValue("@ApprovedUser", CurrentUser)
                    cmd.ExecuteNonQuery()


                    '2) Cr. Revenues
                    cmd.CommandText = "Insert into Transactions (TransType,Source,MoveNo,AccNo,Acc1,Acc2,Acc3,Acc4,Descr,TotalIn," & _
                                      "SavedUser,TransDate,Branch,ApprovedUser,ApprovedDate) Values (@TransType,@Source,@MoveNo,@AccNo,@Acc1," & _
                                      "@Acc2,@Acc3,@Acc4,@Descr,@TotalIn,@SavedUser,GetDate(),@Branch,@ApprovedUser,GetDate())"

                    cmd.Parameters.Clear()
                    cmd.Parameters.AddWithValue("@TransType", "Journal Voucher")
                    cmd.Parameters.AddWithValue("@Source", "P")
                    cmd.Parameters.AddWithValue("@MoveNo", MoveNo)
                    cmd.Parameters.AddWithValue("@AccNo", "410101")
                    cmd.Parameters.AddWithValue("@Acc1", "Revenues")
                    cmd.Parameters.AddWithValue("@Acc2", "Commission")
                    cmd.Parameters.AddWithValue("@Acc3", "Commission Earned from Insurance")
                    cmd.Parameters.AddWithValue("@Acc4", "Commission Earned from Insurance")
                    cmd.Parameters.AddWithValue("@Descr", Descr)
                    cmd.Parameters.AddWithValue("@Branch", Branch)
                    cmd.Parameters.AddWithValue("@TotalIn", CDbl(Me.txtCompComm.Text))
                    cmd.Parameters.AddWithValue("@SavedUser", CurrentUser)
                    cmd.Parameters.AddWithValue("@ApprovedUser", CurrentUser)
                    cmd.ExecuteNonQuery()
                End If

                If Me.GridProducersCommissions.Rows.Count > 0 Then
                    '3) Dr. Expenses
                    cmd.CommandText = "Insert into Transactions (TransType,Source,MoveNo,AccNo,Acc1,Acc2,Acc3,Acc4,Descr,TotalOut," & _
                                      "SavedUser,TransDate,Branch,ApprovedUser,ApprovedDate) Values (@TransType,@Source,@MoveNo,@AccNo,@Acc1," & _
                                      "@Acc2,@Acc3,@Acc4,@Descr,@TotalOut,@SavedUser,GetDate(),@Branch,@ApprovedUser,GetDate())"

                    cmd.Parameters.Clear()
                    cmd.Parameters.AddWithValue("@TransType", "Journal Voucher")
                    cmd.Parameters.AddWithValue("@Source", "P")
                    cmd.Parameters.AddWithValue("@MoveNo", MoveNo)
                    cmd.Parameters.AddWithValue("@AccNo", "580101")
                    cmd.Parameters.AddWithValue("@Acc1", "Expenses")
                    cmd.Parameters.AddWithValue("@Acc2", "Commission Expenses")
                    cmd.Parameters.AddWithValue("@Acc3", "Commission Expenses")
                    cmd.Parameters.AddWithValue("@Acc4", "Brokers Commission")
                    cmd.Parameters.AddWithValue("@Descr", Descr)
                    cmd.Parameters.AddWithValue("@Branch", Branch)
                    'cmd.Parameters.AddWithValue("@TotalOut", CDbl(Me.txtProducerComm.Text))
                    cmd.Parameters.AddWithValue("@SavedUser", CurrentUser)
                    cmd.Parameters.AddWithValue("@ApprovedUser", CurrentUser)
                    cmd.ExecuteNonQuery()


                    '4) Cr. Producer
                    cmd.CommandText = "Insert into Transactions (TransType,Source,MoveNo,AccNo,Acc1,Acc2,Acc3,Acc4,Descr,TotalIn," & _
                                      "SavedUser,TransDate,Branch,ApprovedUser,ApprovedDate) Values (@TransType,@Source,@MoveNo,@AccNo,@Acc1," & _
                                      "@Acc2,@Acc3,@Acc4,@Descr,@TotalIn,@SavedUser,GetDate(),@Branch,@ApprovedUser,GetDate())"

                    cmd.Parameters.Clear()
                    cmd.Parameters.AddWithValue("@TransType", "Journal Voucher")
                    cmd.Parameters.AddWithValue("@Source", "P")
                    cmd.Parameters.AddWithValue("@MoveNo", MoveNo)
                    cmd.Parameters.AddWithValue("@AccNo", GetProducerAccNo(Me.CombProducers.Text))
                    cmd.Parameters.AddWithValue("@Acc1", "Liabilities")
                    cmd.Parameters.AddWithValue("@Acc2", "Current Liabilities")
                    cmd.Parameters.AddWithValue("@Acc3", "Accounts Payable (Sub-Brokers)")
                    cmd.Parameters.AddWithValue("@Acc4", Me.CombProducers.Text)
                    cmd.Parameters.AddWithValue("@Descr", Descr)
                    cmd.Parameters.AddWithValue("@Branch", Branch)
                    'cmd.Parameters.AddWithValue("@TotalIn", CDbl(Me.txtProducerComm.Text))
                    cmd.Parameters.AddWithValue("@SavedUser", CurrentUser)
                    cmd.Parameters.AddWithValue("@ApprovedUser", CurrentUser)
                    cmd.ExecuteNonQuery()
                End If

                Trans.Commit()
                cnn.Close()

                MsgBox("Approved Successfully")

                PrintVoucher(Branch, MoveNo, Me.DTPTrans.Value.Year)

                FillPendingTransactions()
                Clear()

                Me.Cursor = Cursors.Default
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
                MsgBox(ex.ToString)
            End Try
        End If
    End Sub

    Private Sub btnRefreshPendTrans_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRefreshPendTrans.Click
        FillPendingTransactions()
    End Sub

    Private Sub ChAccPath_CheckedChanged(sender As System.Object, e As System.EventArgs) Handles ChAccPath.CheckedChanged
        If Me.ChAccPath.Checked = True Then
            Me.DGColAcc1.Visible = True
            Me.DGColAcc2.Visible = True
            Me.DGColAcc3.Visible = True
        ElseIf Me.ChAccPath.Checked = False Then
            Me.DGColAcc1.Visible = False
            Me.DGColAcc2.Visible = False
            Me.DGColAcc3.Visible = False
        End If
    End Sub

    Private Sub GridVoucher_CellClick_1(sender As System.Object, e As System.Windows.Forms.DataGridViewCellEventArgs) Handles GridVoucher.CellClick
        If e.ColumnIndex = Me.DGColSearch.Index Then
            Try
                Dim a As New frmSelectAccount
                a.ShowDialog()

                If SelAccNo <> "" Then
                    Me.Cursor = Cursors.WaitCursor

                    'Get acc path
                    Dim AccNames(3) As String
                    AccNames = GetAccNames(SelAccNo)

                    Me.GridVoucher.CurrentRow.Cells(Me.DGColAccNo.Index).Value = SelAccNo
                    Me.GridVoucher.CurrentRow.Cells(Me.DGColAcc1.Index).Value = AccNames(0)
                    Me.GridVoucher.CurrentRow.Cells(Me.DGColAcc2.Index).Value = AccNames(1)
                    Me.GridVoucher.CurrentRow.Cells(Me.DGColAcc3.Index).Value = AccNames(2)
                    Me.GridVoucher.CurrentRow.Cells(Me.DGColAcc4.Index).Value = AccNames(3)

                    Me.Cursor = Cursors.Default
                End If
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                MsgBox(ex.ToString)
            End Try

        ElseIf e.ColumnIndex = Me.DGColDelete.Index Then
            If e.RowIndex < (Me.GridVoucher.Rows.Count - 1) Then
                Me.GridVoucher.Rows.RemoveAt(e.RowIndex)
            End If
        End If
    End Sub

    Private Sub GridVoucher_CellEndEdit(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles GridVoucher.CellEndEdit
        If e.ColumnIndex = Me.DGColAccNo.Index Then
            Try
                If Me.GridVoucher.CurrentCell.Value <> "" Then
                    Me.Cursor = Cursors.WaitCursor

                    'Get acc path
                    Dim AccNames(3) As String
                    AccNames = GetAccNames(Me.GridVoucher.CurrentCell.Value)

                    Me.GridVoucher.CurrentRow.Cells(Me.DGColAcc1.Index).Value = AccNames(0)
                    Me.GridVoucher.CurrentRow.Cells(Me.DGColAcc2.Index).Value = AccNames(1)
                    Me.GridVoucher.CurrentRow.Cells(Me.DGColAcc3.Index).Value = AccNames(2)
                    Me.GridVoucher.CurrentRow.Cells(Me.DGColAcc4.Index).Value = AccNames(3)

                    Me.Cursor = Cursors.Default
                Else
                    Me.GridVoucher.CurrentRow.Cells(Me.DGColAcc1.Index).Value = ""
                    Me.GridVoucher.CurrentRow.Cells(Me.DGColAcc2.Index).Value = ""
                    Me.GridVoucher.CurrentRow.Cells(Me.DGColAcc3.Index).Value = ""
                    Me.GridVoucher.CurrentRow.Cells(Me.DGColAcc4.Index).Value = ""
                End If
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                MsgBox(ex.ToString)
            End Try

        ElseIf e.ColumnIndex = Me.DGColDebit.Index Then
            If Me.GridVoucher.CurrentCell.Value = "" Then
                Me.GridVoucher.CurrentCell.Value = "0.00"
            Else
                Try
                    Me.GridVoucher.CurrentCell.Value = CDbl(Me.GridVoucher.CurrentCell.Value).ToString("N2")
                    If CDbl(Me.GridVoucher.CurrentCell.Value) <> 0 Then
                        Me.GridVoucher.CurrentRow.Cells(Me.DGColCredit.Index).Value = "0.00"
                    End If
                Catch ex As Exception
                    Me.GridVoucher.CurrentCell.Value = "0.00"
                End Try
            End If

        ElseIf e.ColumnIndex = Me.DGColCredit.Index Then
            If Me.GridVoucher.CurrentCell.Value = "" Then
                Me.GridVoucher.CurrentCell.Value = "0.00"
            Else
                Try
                    Me.GridVoucher.CurrentCell.Value = CDbl(Me.GridVoucher.CurrentCell.Value).ToString("N2")
                    If CDbl(Me.GridVoucher.CurrentCell.Value) <> 0 Then
                        Me.GridVoucher.CurrentRow.Cells(Me.DGColDebit.Index).Value = "0.00"
                    End If
                Catch ex As Exception
                    Me.GridVoucher.CurrentCell.Value = "0.00"
                End Try
            End If

        End If

        Calculate()
        ValidateGrid()
    End Sub

    Sub ValidateGrid()
        Try
            Me.Cursor = Cursors.WaitCursor

            For Each row As DataGridViewRow In Me.GridVoucher.Rows
                If row.Cells(4).Value = "" Then
                    row.Cells(4).Style.BackColor = Color.Salmon
                Else
                    row.Cells(4).Style.BackColor = row.DefaultCellStyle.BackColor
                End If

                If CDbl(row.Cells(9).Value) = 0 And CDbl(row.Cells(10).Value) = 0 Then
                    row.Cells(9).Style.BackColor = Color.Salmon
                    row.Cells(10).Style.BackColor = Color.Salmon

                ElseIf CDbl(row.Cells(9).Value) = 0 Or CDbl(row.Cells(10).Value) = 0 Then
                    row.Cells(9).Style.BackColor = row.DefaultCellStyle.BackColor
                    row.Cells(10).Style.BackColor = row.DefaultCellStyle.BackColor
                End If
            Next

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
        End Try
    End Sub

    Sub Calculate()
        If Me.GridVoucher.Rows.Count = 0 Then
            Me.txtCrd.Text = "0"
            Me.txtDep.Text = "0"
            Me.txtBalance.Text = "0"
        Else
            Try
                Dim Crd, Dep As Double
                Dim i As Integer

                ' Iterate through a dictionary
                For i = 0 To Me.GridVoucher.Rows.Count - 1
                    Dep = Dep + Me.GridVoucher.Rows(i).Cells(Me.DGColDebit.Index).Value
                    Crd = Crd + Me.GridVoucher.Rows(i).Cells(Me.DGColCredit.Index).Value
                Next

                If Crd = 0 Then
                    Me.txtCrd.Text = "0.00"
                Else
                    Me.txtCrd.Text = Format(Crd, "##,###.##")
                End If

                If Dep = 0 Then
                    Me.txtDep.Text = "0.00"
                Else
                    Me.txtDep.Text = Format(Dep, "##,###.##")
                End If

                If Crd - Dep = 0 Then
                    Me.txtBalance.Text = "0.00"
                Else
                    Me.txtBalance.Text = Format(CDbl(Crd - Dep), "##,###.##")
                End If
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
        End If
    End Sub
End Class